/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package onpu_dms_xls.kernel.common.pdf.composite;

import onpu_dms_xls.exceptions.MyException;
import com.itextpdf.text.Document;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfContentByte;
import com.itextpdf.text.pdf.PdfWriter;
import java.io.*;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import onpu_dms_xls.exceptions.MySQLException;
import onpu_dms_xls.exceptions.MyXMLException;
import onpu_dms_xls.kernel.common.Util;
import onpu_dms_xls.kernel.common.VariableDeclaration;
import onpu_dms_xls.kernel.common.VariableType_DBRelationTypes;
import onpu_dms_xls.kernel.common.VariableType_InputRelationTypes;
import onpu_dms_xls.kernel.common.VariableType_XLSVisibilityTypes;
import onpu_dms_xls.kernel.common.inputParameters.FromDB_InputParameters;
import onpu_dms_xls.kernel.common.logAndDebug.Debug;
import onpu_dms_xls.kernel.common.vipiska.*;
import onpu_dms_xls.kernel.common.inputParameters.Variable;
import onpu_dms_xls.kernel.common.logAndDebug.LoggingFacade;
import org.apache.log4j.Logger;

/**
 *
 * @author snbm
 */
public class new_PDF_ExtractCreator implements VariableValueForLabelGetter{
    

    private String fileName;
    private ExtractComponent pdf_temlateAndData;
    private VariableDeclaration[] varDeclArr;
    FromDB_InputParameters input;
    Connection con;
    
    public new_PDF_ExtractCreator(String fileName,
            ExtractComponent pdf_temlate,
            VariableDeclaration[] varDeclArr,
            FromDB_InputParameters input,
            Connection con) {
        LoggingFacade.trace.info("new_PDF_Extract_Creator creation, fileName = " + fileName);
        this.fileName = new String(fileName);
        this.pdf_temlateAndData = pdf_temlate;
        this.varDeclArr = varDeclArr;
        this.input = input;
        this.con = con;
        
    }

    public File makeFile() throws Exception {
        LoggingFacade.trace.debug("\tmakeFile start");

        ExtractWriter ew = ExtractWriter.createPDF_ExtractWriter(fileName, this);

        LoggingFacade.trace.debug("\tmakeFile got PDF_ExtractWriter"+ew);

        pdf_temlateAndData.printToPDFWriter((PDF_ExtractWriter)ew);

        ew.print();

        LoggingFacade.trace.info("makeFile returning result, fileName="+fileName);

        return new File(fileName);
    }

    @Override
    public String getVariableValueForLabel(ExtractLabel label) {
        LoggingFacade.trace.debug("getVariableValueForLabel call, labelName: " + label.getVariableName());

        for (VariableDeclaration vd : varDeclArr) {
            if (vd.getVarName().equals(label.getVariableName())) {
                LoggingFacade.trace.debug("found corresponding variable declaration=" + vd.getVarName()+" it's value="+ vd.getValue());
                return vd.getValue();
            }
        }
        throw new RuntimeException("not found corresponding variable declaration for label: " + label.getVariableName());
    }

    
    /**
    головний метод отримання даних з БД.
     */
    public void loadDataFromDBToVipiska_Data() throws MyException {
        //для змінних з значенням в input (0 рівень) - присвоїти ці значення
        //це все спеціально для student_id. більше нічого такого в мене поки немає.
        LoggingFacade.trace.debug("call");
        for (VariableDeclaration vd : varDeclArr) {
            if (!vd.isInIteration()
                    && vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.ALWAYS_IN_INPUT)
                    && vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.NONE)
                    && vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.NOT_VISIBLE)) {

                LoggingFacade.trace.debug("found 0 level variable (student_id): " + vd.getVarName());
                LoggingFacade.trace.debug("finding a value in input for it;");
                for (Variable v : input.getOtherVars()) {
                    LoggingFacade.trace.debug("\t" + v.getName());
                    if (v.getName().equals(vd.getVarName())) {
                        LoggingFacade.trace.debug("found corresponding variable in input!!!!!!");
                        vd.setValue(v.getValue());
                        LoggingFacade.trace.debug("new value for " + vd.getVarName() + " is " + vd.getValue());
                        break;
                    }
                }
            }
        }


        //тепер для змінних 0 рівня, зі значенням на основі запиту в БД
        //поки що тільки для kafedra_speciality_id
        for (VariableDeclaration vd : varDeclArr) {
            if (!vd.isInIteration()
                    && vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT)
                    && vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.SELECT)
                    && vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.NOT_VISIBLE)) {

                LoggingFacade.trace.debug("found 0 level variable (kafedra_speciality_id): " + vd.getVarName());

                //1. getQuery
                String fromDBQuery = vd.getFromDBQuery();
                //2. replace $ parameters
                LoggingFacade.trace.debug("fromDBQuery BEFORE REPLACEMENT:" + fromDBQuery);
                fromDBQuery = Util.fillQueryWithVariablesValues(fromDBQuery, varDeclArr);
                LoggingFacade.trace.debug("fromDBQuery AFTER REPLACEMENT:" + fromDBQuery);
                //3. execute Query, get variable value ----------temp - dataTypes things....
                String queryResult = "";
                try {
                    Statement st = con.createStatement();
                    ResultSet rs = st.executeQuery(fromDBQuery);
                    rs.next();
                    queryResult = rs.getString(1);

                } catch (SQLException e) {
                    LoggingFacade.trace.error("EXCEPTION:", e);

                    //that happens
                    if (e.getMessage().equals("ResultSet not positioned properly, perhaps you need to call next.")) {
                        queryResult = "n/d";
                    } else {
                        throw new MySQLException(
                                "PDF_Extract_Creator",
                                "loadDataFromDBToVipiska_Data",
                                fromDBQuery,
                                "",
                                e.getMessage());
                    }
                }
                LoggingFacade.trace.debug("queryResult:" + queryResult);
                vd.setValue(queryResult);
                LoggingFacade.trace.debug("new value for " + vd.getVarName() + " is " + vd.getValue());
            }
        }

        //тепер для змінних 1 рівня, просто ганяємо запити до БД...
        for (VariableDeclaration vd : varDeclArr) {
            if (!vd.isInIteration()
                    && vd.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT)
                    && vd.getVarType().getDB_Relation().equals(VariableType_DBRelationTypes.SELECT)
                    && vd.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {

                LoggingFacade.trace.debug("found 1 level variable: " + vd.getVarName());

                //1. getQuery
                String fromDBQuery = vd.getFromDBQuery();
                //2. replace $ parameters
                LoggingFacade.trace.debug("fromDBQuery BEFORE REPLACEMENT:" + fromDBQuery);
                fromDBQuery = Util.fillQueryWithVariablesValues(fromDBQuery, varDeclArr);
                LoggingFacade.trace.debug("fromDBQuery AFTER REPLACEMENT:" + fromDBQuery);
                //3. execute Query, get variable value ----------temp - dataTypes things....
                String queryResult = "";
                try {
                    Statement st = con.createStatement();
                    ResultSet rs = st.executeQuery(fromDBQuery);
                    rs.next();
                    queryResult = rs.getString(1);

                } catch (SQLException e) {
                    //that happens
                    if (e.getMessage().equals("ResultSet not positioned properly, perhaps you need to call next.")) {
                        queryResult = "n/d";
                    } else {
                        throw new MySQLException(
                                "PDF_Extract_Creator",
                                "loadDataFromDBToVipiska_Data",
                                fromDBQuery,
                                "",
                                e.getMessage());
                    }
                }
                LoggingFacade.trace.debug("queryResult:" + queryResult);
                if (queryResult == null){
                    LoggingFacade.trace.warn("replacing query result with empty string since queryResult IS NULL for query="+fromDBQuery);
                    queryResult = "";
                }
                vd.setValue(queryResult);
                LoggingFacade.trace.debug("new value for " + vd.getVarName() + " is " + vd.getValue());
            }
        }



        //______________________________MARKSSSSSSSSSSSSS
        String fromDB_IterationRowQuery = null;

        ArrayList<VariableDeclaration> iterRowVarDecl = new ArrayList<VariableDeclaration>();

        for (VariableDeclaration varDec : varDeclArr) {
            if (varDec.isInIteration()
                    && varDec.getVarType().getInput_Relation().equals(VariableType_InputRelationTypes.NEVER_IN_INPUT)
                    && varDec.getVarType().getXLS_Visibility().equals(VariableType_XLSVisibilityTypes.VISIBLE)) {
                iterRowVarDecl.add(varDec);
                fromDB_IterationRowQuery = varDec.getIterationRowSingleFromDBQuery();
            }
        }

        if (iterRowVarDecl.size() == 0) {
            throw new MyXMLException(
                    "PDF_EXtract_Creator",
                    "getData....",
                    "no iterable variables found in variable declaration array, formed from template xml",
                    "");
        }

        if (fromDB_IterationRowQuery == null) {
            throw new RuntimeException(" NO ITERATION QUERY FOUND FOR ITERATION VARIABLES");
        }


        //2. replace $ parameters
        LoggingFacade.trace.debug("fromDBQuery BEFORE REPLACEMENT:" + fromDB_IterationRowQuery);
        fromDB_IterationRowQuery = Util.fillQueryWithVariablesValues(fromDB_IterationRowQuery, varDeclArr);
        LoggingFacade.trace.debug("fromDBQuery AFTER REPLACEMENT:" + fromDB_IterationRowQuery);

        LoggingFacade.trace.debug("\tloadData1");
        //3. execute Query, get variable value ----------
        ResultSet rs;
        try {
            Statement st = con.createStatement();
            rs = st.executeQuery(fromDB_IterationRowQuery);
        } catch (SQLException e) {
            LoggingFacade.trace.error("newPDFExtractCreator", e);
            throw new MySQLException(
                    "PDF_Extract_Creator",
                    "loadDataFromDBToVipiska_Data",
                    fromDB_IterationRowQuery,
                    "",
                    e.getMessage());
        }
        ArrayList<Extract_Subject> sub = new ArrayList<Extract_Subject>();
        //4. throw it to sub
        LoggingFacade.trace.debug("\tloadData2");
        try {
            while (rs.next()) {
                int control_id = rs.getInt("control_id");
                String subjecName = rs.getString("name") == null ? "" : rs.getString("name");
                int hours = rs.getInt("hours");
                int mark = rs.getInt("mark");

                if (control_id == 3) {
                    subjecName = "Курсова робота: " + subjecName;
                    hours = 30;
                } else if (control_id == 4) {
                    hours = 60;
                }

                sub.add(new Extract_Subject(control_id, subjecName, hours, mark));
            }
        } catch (Exception e) {
            throw new RuntimeException(e.getMessage());
        }

        Iterator<ExtractComponent> it = pdf_temlateAndData.getIterator();
        while (it.hasNext()) {
            ExtractComponent c = it.next();
            if (c instanceof ExtractMarkLabels) {
                ExtractMarkLabels label = ((ExtractMarkLabels) c);
                label.setSubjects(sub);
            }
        }
        LoggingFacade.trace.debug("\tloadData3");
    }
}
